package com.fowo.api.documentCirculation.sql;

import org.springframework.util.StringUtils;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.stream.BaseStream;
import java.util.stream.Collectors;

/**
 * SQL 生成处理器的基类
 * @author yl_ls
 */
public abstract class AbsoluteSqlHandler implements ISqlHandler {
    @Override
    public SqlAndArgs getSql(SqlBuilder sqlBuilder) throws Exception {
        switch (sqlBuilder.getQueryKind()) {
            case Select:
                return createSelectSql(sqlBuilder);
            case Insert:
                return createInsertSql(sqlBuilder);
            case Update:
                return createUpdateSql(sqlBuilder);
            case Delete:
                return createDeleteSql(sqlBuilder);
            default:
                throw new Exception("不支持此查询类型");
        }
    }

    @Override
    public SqlAndArgs getCountSql(SqlBuilder sqlBuilder) throws Exception {
        SqlAndArgs sqlAndArgs = new SqlAndArgs();
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT count(0) ");
        sb.append("FROM ");
        if (!sqlBuilder.getGroupBys().isEmpty()) {
            sb.append("(SELECT ").append(String.join(", ", sqlBuilder.getGroupBys())).append(" FROM");
        }
        sb.append(safeName(sqlBuilder.getTable()));
        if (StringUtils.hasText(sqlBuilder.getAsName())) {
            sb.append(" AS ").append(safeName(sqlBuilder.getAsName()));
        }
        appendJoinsSql(sb, sqlBuilder, sqlAndArgs);
        appendWhereSql(sb, sqlBuilder, sqlAndArgs);
        appendGroupBySql(sb, sqlBuilder, sqlAndArgs);
        if (!sqlBuilder.getGroupBys().isEmpty()) {
            sb.append(") AS TMP");
        }
        sqlAndArgs.setSql(sb.toString());
        return sqlAndArgs;
    }

    @Override
    public SqlAndArgs getStatisticsSql(SqlBuilder sqlBuilder, List<SqlBuilder.StatisticsColumn> columns) throws Exception {
        SqlAndArgs sqlAndArgs = new SqlAndArgs();
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ");
        boolean hasOutput = false;
        for (SqlBuilder.StatisticsColumn column : columns) {
            if (hasOutput) {
                sb.append(", ");
            }
            if (column.getKind() == SqlBuilder.StatisticsKind.Expression) {
                sb.append(String.format("%s AS %s", column.getColumn(), safeName(column.getAsName())));
            }
            else {
                sb.append(String.format("%s AS %s", statisticsColumnToExpression(sqlBuilder, column), safeName(column.getAsName())));
            }
            hasOutput = true;
        }
        sb.append(" FROM ");
        if (!sqlBuilder.getGroupBys().isEmpty()) {
            sb.append("(SELECT ").append(String.join(", ", sqlBuilder.getGroupBys())).append(" FROM");
        }
        sb.append(safeName(sqlBuilder.getTable()));
        if (StringUtils.hasText(sqlBuilder.getAsName())) {
            sb.append(" AS ").append(safeName(sqlBuilder.getAsName()));
        }
        appendJoinsSql(sb, sqlBuilder, sqlAndArgs);
        appendWhereSql(sb, sqlBuilder, sqlAndArgs);
        appendGroupBySql(sb, sqlBuilder, sqlAndArgs);
        if (!sqlBuilder.getGroupBys().isEmpty()) {
            sb.append(") AS TMP");
        }
        sqlAndArgs.setSql(sb.toString());
        return sqlAndArgs;
    }

    /**
     * 生成一般统计代码
     * @param sqlBuilder 所在 SqlBuilder 实例
     * @param column 统计列信息
     * @return 统计列 SQL 选择段
     */
    protected String statisticsColumnToExpression(SqlBuilder sqlBuilder, SqlBuilder.StatisticsColumn column) {
        String statFunc = null;
        if (column.getKind() == SqlBuilder.StatisticsKind.Sum) {
            statFunc = "SUM(%s)";
        }
        else if (column.getKind() == SqlBuilder.StatisticsKind.Avg) {
            statFunc = "AVG(%s)";
        }
        else if (column.getKind() == SqlBuilder.StatisticsKind.Max) {
            statFunc = "MAX(%s)";
        }
        else if (column.getKind() == SqlBuilder.StatisticsKind.Min) {
            statFunc = "MIN(%s)";
        }
        else {
            throw new RuntimeException("StatisticsKind is unknown");
        }
        if (column.isColumnIsExpression() || !StringUtils.hasText(sqlBuilder.getAsName())) {
            return String.format(statFunc, column.getColumn());
        }
        return String.format(statFunc, String.format("%s.%s", sqlBuilder.getAsName(), column.getColumn()));
    }

    @Override
    public String funcIf(String conditionExp, String trueExp, String falseExp) {
        return String.format("IF(%s, %s, %s)", conditionExp, trueExp, falseExp);
    }

    @Override
    public String funcIfNull(String valueExp, String nullExp) {
        return String.format("IFNULL(%s, %s)", valueExp, nullExp);
    }

    private SqlAndArgs createDeleteSql(SqlBuilder sqlBuilder) {
        SqlAndArgs sqlAndArgs = new SqlAndArgs();
        StringBuilder sb = new StringBuilder();
        sb.append("DELETE FROM ").append(safeName(sqlBuilder.getTable()));
        appendWhereSql(sb, sqlBuilder, sqlAndArgs);
        sqlAndArgs.setSql(sb.toString());
        return sqlAndArgs;
    }

    public SqlAndArgs createUpdateSql(SqlBuilder sqlBuilder) {
        SqlAndArgs sqlAndArgs = new SqlAndArgs();
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE ").append(safeName(sqlBuilder.getTable()));
        sb.append(" SET ");
        appendSetFieldParts(sb, sqlBuilder, sqlAndArgs);
        appendWhereSql(sb, sqlBuilder, sqlAndArgs);
        sqlAndArgs.setSql(sb.toString());
        return sqlAndArgs;
    }

    public void appendSetFieldParts(StringBuilder sb, SqlBuilder sqlBuilder, SqlAndArgs sqlAndArgs) {
        boolean needSplitter = false;
        for (Map.Entry<String, Object> nameValueEntry : sqlBuilder.getValues().entrySet()) {
            if (needSplitter) {
                sb.append(", ");
            }
            sb.append(safeName(nameValueEntry.getKey()));
            if (nameValueEntry.getValue() == null) {
                sb.append(" = null");
            } else {
                sb.append(" = ?");
                sqlAndArgs.addArgs(convertToSqlArg(nameValueEntry.getValue()));
            }
            needSplitter = true;
        }
    }

    public SqlAndArgs createInsertSql(SqlBuilder sqlBuilder) {
        SqlAndArgs sqlAndArgs = new SqlAndArgs();
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO ");
        sb.append(safeName(sqlBuilder.getTable()));
        appendInsertFieldParts(sb, sqlBuilder, sqlAndArgs);
        sqlAndArgs.setSql(sb.toString());
        return sqlAndArgs;
    }

    public void appendInsertFieldParts(StringBuilder sb, SqlBuilder sqlBuilder, SqlAndArgs sqlAndArgs) {
        StringBuilder sbNames = new StringBuilder();
        StringBuilder sbValues = new StringBuilder();
        for (Map.Entry<String, Object> nameValueEntry : sqlBuilder.getValues().entrySet()) {
            if (sbNames.length() > 0) {
                sbNames.append(", ");
                sbValues.append(", ");
            }
            sbNames.append(safeName(nameValueEntry.getKey()));
            if (nameValueEntry.getValue() == null) {
                sbValues.append("NULL");
            } else {
                sbValues.append("?");
                sqlAndArgs.addArgs(convertToSqlArg(nameValueEntry.getValue()));
            }
        }
        sb.append("(").append(sbNames).append(") VALUES (").append(sbValues).append(")");
    }

    public SqlAndArgs createSelectSql(SqlBuilder sqlBuilder) {
        SqlAndArgs sqlAndArgs = new SqlAndArgs();
        StringBuilder sb = new StringBuilder();
        appendSelectStart(sb, sqlBuilder);
        sb.append("SELECT ");
        appendSelectColumns(sb, sqlBuilder);
        sb.append(" ");
        sb.append("FROM ");
        sb.append(safeName(sqlBuilder.getTable()));
        if (StringUtils.hasText(sqlBuilder.getAsName())) {
            sb.append(" AS ").append(safeName(sqlBuilder.getAsName()));
        }
        appendJoinsSql(sb, sqlBuilder, sqlAndArgs);
        appendWhereSql(sb, sqlBuilder, sqlAndArgs);
        appendGroupBySql(sb, sqlBuilder, sqlAndArgs);
        appendSelectEnd(sb, sqlBuilder);
        sqlAndArgs.setSql(sb.toString());
        return sqlAndArgs;
    }

    protected void appendGroupBySql(StringBuilder sb, SqlBuilder sqlBuilder, SqlAndArgs sqlAndArgs) {
        if (sqlBuilder.getGroupBys().isEmpty()) {
            return;
        }
        sb.append(" GROUP BY ");
        sb.append(String.join(", ", sqlBuilder.getGroupBys()));
        sb.append(" ");
    }

    protected void appendJoinsSql(StringBuilder sb, SqlBuilder sqlBuilder, SqlAndArgs sqlAndArgs) {
        if (sqlBuilder.getJoinBuilders().isEmpty()) {
            return;
        }
        for (SqlBuilder.JoinBuilder joinBuilder : sqlBuilder.getJoinBuilders()) {
            if (joinBuilder.getJoinKind() == SqlBuilder.JoinKind.Sql) {
                sb.append(" ").append(joinBuilder.getSql());
            } else {
                sb.append(joinBuilder.getJoinKind() == SqlBuilder.JoinKind.Left ? " LEFT" : " INNER")
                        .append(" JOIN ")
                        .append(safeName(joinBuilder.getSqlBuilder().getTable()))
                        .append(" AS ")
                        .append(safeName(joinBuilder.getSqlBuilder().getAsName()));
                appendJoinOnSql(sb, sqlBuilder, sqlAndArgs, joinBuilder);
            }
        }
    }

    private void appendJoinOnSql(StringBuilder sb, SqlBuilder sqlBuilder, SqlAndArgs sqlAndArgs, SqlBuilder.JoinBuilder joinBuilder) {
        if (!joinBuilder.getOnParts().isEmpty()) {
            sb.append(" ON ");
            boolean addAnd = false;
            for (SqlBuilder.JoinOnPart onPart : joinBuilder.getOnParts()) {
                if (addAnd) {
                    sb.append(" AND ");
                }
                if (!StringUtils.hasText(onPart.getLeftColumn())) {
                    onPart.setNoConnector(true);
                } else if(onPart.getLeftColumn() instanceof SqlBuilder.JoinOnConstValue) {
                    sb.append(convertToSqlString(onPart.getLeftColumn()));
                } else if (StringUtils.hasText(onPart.getLeftTable())) {
                    sb.append(safeName(onPart.getLeftTable()))
                            .append(".")
                            .append(safeName(onPart.getLeftColumn().toString()));
                } else {
                    sb.append(safeName("t"))
                            .append(".")
                            .append(safeName(onPart.getLeftColumn().toString()));
                }

                if (Boolean.TRUE.equals(onPart.getNoConnector())) {
                    sb.append(onPart.getRightColumn());
                }
                else {
                    sb.append(" = ");

                    if (onPart.getRightColumn() instanceof SqlBuilder.JoinOnConstValue) {
                        sb.append(convertToSqlString(onPart.getRightColumn()));
                    } else if (StringUtils.hasText(onPart.getRightTable())) {
                        sb.append(safeName(onPart.getRightTable()))
                                .append(".")
                                .append(safeName(onPart.getRightColumn().toString()));
                    } else {
                        sb.append(safeName(joinBuilder.getSqlBuilder().getAsName()))
                                .append(".")
                                .append(safeName(onPart.getRightColumn().toString()));
                    }
                }
                addAnd = true;
            }
        }
    }

    protected String convertToSqlString(CharSequence charSequence) {
        if (charSequence == null) {
            return "NULL";
        }
        return String.format("'%s'", charSequence.toString().replaceAll("'", "''"));
    }

    protected void appendSelectStart(StringBuilder sb, SqlBuilder sqlBuilder) {
    }

    protected void appendSelectEnd(StringBuilder sb, SqlBuilder sqlBuilder) {
    }

    protected void appendSelectColumns(StringBuilder sb, SqlBuilder sqlBuilder) {
        appendSelectColumns(sb, sqlBuilder, true);
        removeEndComma(sb);
    }

    private void removeEndComma(StringBuilder sb) {
        while(sb.length() > 0 && sb.charAt(sb.length() - 1) == ' ') {
            sb.setLength(sb.length() - 1);
        }
        if (',' == sb.charAt(sb.length() - 1)) {
            sb.setLength(sb.length() - 1);
        }
    }

    protected void appendSelectColumns(StringBuilder sb, SqlBuilder sqlBuilder, boolean allowAppendStar) {
        boolean addSplitter = false;
        int startLength = sb.length();
        for (SqlBuilder.SelectColumn column : sqlBuilder.getSelectColumns()) {
            if (addSplitter) {
                sb.append(", ");
            }
            if (column.isConvertToString()) {
                appendConvertToStringStart(sb);
            }
            if (StringUtils.hasText(column.getExpression())) {
                sb.append(column.getExpression());
            } else {
                if (StringUtils.hasText(sqlBuilder.getAsName())) {
                    sb.append(safeName(sqlBuilder.getAsName())).append(".");
                }
                sb.append(safeName(column.getName()));
            }
            if (column.isConvertToString()) {
                appendConvertToStringEnd(sb);
            }
            if (StringUtils.hasText(column.getAsName())) {
                sb.append(" AS ").append(safeName(column.getAsName()));
            }
            addSplitter = true;
        }
        for (SqlBuilder.JoinBuilder joinBuilder : sqlBuilder.getJoinBuilders()) {
            if (joinBuilder.getJoinKind() == SqlBuilder.JoinKind.Sql) {
                continue;
            }
            if (addSplitter) {
                sb.append(", ");
            }
            appendSelectColumns(sb, joinBuilder.getSqlBuilder(), false);
            addSplitter = true;
        }
        if (addSplitter && !allowAppendStar) {
            removeEndComma(sb);
        }
        if (allowAppendStar && startLength == sb.length()) {
            sb.append("*");
        }
    }

    protected abstract void appendConvertToStringEnd(StringBuilder sb);

    protected abstract void appendConvertToStringStart(StringBuilder sb);

    public void appendWhereSql(StringBuilder sb, SqlBuilder sqlBuilder, SqlAndArgs sqlAndArgs) {
        if (sqlBuilder.getConditionParts().isEmpty()) {
            return;
        }
        sb.append(" WHERE ");
        boolean needAnd = false;
        for (SqlBuilder.ConditionPart conditionPart : sqlBuilder.getConditionParts()) {
            if (needAnd) {
                sb.append(" AND ");
            }
            appendWherePart(sb, sqlBuilder, sqlAndArgs, conditionPart);
            needAnd = true;
        }
        for (SqlBuilder.JoinBuilder joinBuilder : sqlBuilder.getJoinBuilders()) {
            if (joinBuilder.getSqlBuilder() == null || joinBuilder.getSqlBuilder().getConditionParts().isEmpty()) {
                continue;
            }
            for (SqlBuilder.ConditionPart conditionPart : joinBuilder.getSqlBuilder().getConditionParts()) {
                if (needAnd) {
                    sb.append(" AND ");
                }
                appendWherePart(sb, joinBuilder.getSqlBuilder(), sqlAndArgs, conditionPart);
                needAnd = true;
            }
        }
    }

    private void appendWherePart(StringBuilder sb, SqlBuilder sqlBuilder, SqlAndArgs sqlAndArgs, SqlBuilder.ConditionPart conditionPart) {
        if (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.OrGroup) {
            List<SqlBuilder.ConditionPart> orParts = (List<SqlBuilder.ConditionPart>) conditionPart.getConditionValue();
            sb.append(" (");
            boolean addOr = false;
            for (SqlBuilder.ConditionPart orPart : orParts) {
                if (addOr) {
                    sb.append(" OR ");
                }
                appendWherePart(sb, sqlBuilder, sqlAndArgs, orPart);
                addOr = true;
            }
            sb.append(") ");
            return;
        }
        if (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.AndGroup) {
            List<SqlBuilder.ConditionPart> orParts = (List<SqlBuilder.ConditionPart>) conditionPart.getConditionValue();
            sb.append(" (");
            boolean addOr = false;
            for (SqlBuilder.ConditionPart orPart : orParts) {
                if (addOr) {
                    sb.append(" AND ");
                }
                appendWherePart(sb, sqlBuilder, sqlAndArgs, orPart);
                addOr = true;
            }
            sb.append(") ");
            return;
        }
        if (StringUtils.hasText(conditionPart.getTableAsName())) {
            sb.append(safeName(conditionPart.getTableAsName())).append(".");
        }
        else if (conditionPart.getColumnName() instanceof String && StringUtils.hasText(sqlBuilder.getAsName())) {
            sb.append(safeName(sqlBuilder.getAsName())).append(".");
        }
        if (conditionPart.getColumnName() instanceof String) {
            sb.append(safeName(conditionPart.getColumnName().toString()));
        } else if (conditionPart.getColumnName() != null) {
            sb.append(conditionPart.getColumnName());
        }
        if (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.Exp) {
            sb.append(" ").append(conditionPart.getConditionValue());
            return;
        } else if (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.Like) {
            sb.append(" LIKE CONCAT('%', ?, '%')");
            sqlAndArgs.addArgs(convertToSqlArg(conditionPart.getConditionValue()));
            return;
        } else if (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.Equals) {
            sb.append(" = ");
        } else if (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.NotEquals) {
            sb.append(" != ");
        } else if (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.GreaterThan) {
            sb.append(" > ");
        } else if (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.In) {
            sb.append(" in ");
        } else if (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.NotIn) {
            sb.append(" not in ");
        } else if (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.IsNotNull) {
            sb.append(" is not null");
            return;
        } else if (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.IsNull) {
            sb.append(" is null");
            return;
        } else {
            throw new RuntimeException(conditionPart.getConditionKind() + " 条件类型未被支持");
        }
        if (conditionPart.getConditionValue() instanceof SqlBuilder) {
            SqlBuilder subSqlBuilder = (SqlBuilder) conditionPart.getConditionValue();
            sb.append("(SELECT ");
            appendSelectColumns(sb, subSqlBuilder);
            sb.append(" FROM ");
            sb.append(safeName(subSqlBuilder.getTable()));
            if(StringUtils.hasText(subSqlBuilder.getAsName())) {
                sb.append(" AS ").append(subSqlBuilder.getAsName());
            }
            appendWhereSql(sb, subSqlBuilder, sqlAndArgs);
            sb.append(")");
        }else if (
                (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.In || conditionPart.getConditionKind() == SqlBuilder.ConditionKind.NotIn)
                        && conditionPart.getConditionValue() != null
                        && List.class.isAssignableFrom(conditionPart.getConditionValue().getClass())
        ) {
            List<?> list = (List<?>) conditionPart.getConditionValue();
            sb.append("(");
            boolean addSplitter = false;
            for(Object item : list) {
                if (addSplitter) {
                    sb.append(", ");
                }
                // JOCK 2024/5/31, In 参数可能太多，大于数据库支持的最大参数量，改为进行安全参数转换为原始 SQL
                sb.append(safeValue(item));
//                sb.append("?");
//                sqlAndArgs.addArgs(convertToSqlArg(item));
                addSplitter = true;
            }
            sb.append(")");
        } else if (
                (conditionPart.getConditionKind() == SqlBuilder.ConditionKind.In || conditionPart.getConditionKind() == SqlBuilder.ConditionKind.NotIn)
                        && conditionPart.getConditionValue() != null
                        && BaseStream.class.isAssignableFrom(conditionPart.getConditionValue().getClass())
        ) {
            BaseStream stream = (BaseStream) conditionPart.getConditionValue();
            final Iterator iterator = stream.iterator();
            sb.append("(");
            boolean addSplitter = false;
            while(iterator.hasNext()) {
                if (addSplitter) {
                    sb.append(", ");
                }
                sb.append("?");
                sqlAndArgs.addArgs(convertToSqlArg(iterator.next()));
                addSplitter = true;
            }
            sb.append(")");
        } else {
            sb.append("?");
            sqlAndArgs.addArgs(convertToSqlArg(conditionPart.getConditionValue()));
        }
    }

    protected Object convertToSqlArg(Object conditionValue) {
        return conditionValue;
    }

    abstract String safeName(String name);

    protected String safeValue(Object value) {
        if (value == null) {
            return "null";
        }
        if (value instanceof String) {
            return "'" + ((String) value).replaceAll("'", "''") + "'";
        }
        if (value instanceof Number) {
            return value.toString();
        }
        if (value instanceof Boolean) {
            return ((Boolean)value) ? "1" : "0";
        }
        throw new RuntimeException("Value Type " + value.getClass().getName() + " Is Not Supper Convert to Sql Value");
    }
}
